package com.offcn.question.service.impl;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.offcn.common.utils.PageUtils;
import com.offcn.common.utils.Query;
import com.offcn.question.dao.QuestionDao;
import com.offcn.question.entity.QuestionEntity;
import com.offcn.question.service.QuestionService;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


@Service("questionService")
public class QuestionServiceImpl extends ServiceImpl<QuestionDao, QuestionEntity> implements QuestionService {

    @Override
    public PageUtils queryPage(Map<String, Object> params) {

        QueryWrapper<QuestionEntity> QueryWrapper = new QueryWrapper<>();
        QueryWrapper.eq("id",params.get("key")).or().like("title",params.get("key")).or().like("answer",params.get("ket"));
        IPage<QuestionEntity> page = this.page(
                new Query<QuestionEntity>().getPage(params),
                QueryWrapper
        );

        return new PageUtils(page);
    }

    @Override
    public Map<String,Object> importExcel(MultipartFile multipartFile) {

        Map<String,Object> map=new HashMap<>();
        //获取文件全名称
        String filename = multipartFile.getOriginalFilename();
        //扩展名.后边参数
        String substring = filename.substring(filename.lastIndexOf(".") + 1);
        if (!substring.equals("xls")&& !substring.equals("xlsx")){
            map.put("result",false);
            map.put("msg","格式错误,请检查格式!");
            return map;
        }
        try {
            XSSFWorkbook workbook = new XSSFWorkbook(multipartFile.getInputStream());
            XSSFSheet sheet = workbook.getSheetAt(0);
            int rows = sheet.getPhysicalNumberOfRows();//含有数据的行数
            for (int i = 1; i < rows; i++) {
                XSSFRow row = sheet.getRow(i);//查行
                QuestionEntity questionEntity = new QuestionEntity(); //查列
                questionEntity.setTitle(row.getCell(0).getStringCellValue());  //题目标题
                questionEntity.setAnswer(row.getCell(1).getStringCellValue());  //解答
                questionEntity.setLevel((int)row.getCell(2).getNumericCellValue()); //难度等级
                questionEntity.setDisplayOrder((int)row.getCell(3).getNumericCellValue()); //排序
                questionEntity.setSubTitle(row.getCell(4).getStringCellValue()); //副标题
                questionEntity.setType((long) row.getCell(5).getNumericCellValue()); //类型
                questionEntity.setEnable((int) row.getCell(6).getNumericCellValue());//是否显示
                        //执行保存
                        this.save(questionEntity);
            }
               map.put("result",true);
               map.put("msg","导入成功");
               map.put("num",rows-1);//保存行数
            return map;
        } catch (IOException e) {
            e.printStackTrace();
            map.put("result",false);
            map.put("msg","导入失败");
            return map;
        }

    }

@Override
public Workbook exportExcel() {
// 创建新的Excel 工作簿
    Workbook workbook = new HSSFWorkbook();
// 在Excel工作簿中建一工作表，其名为缺省值 Sheet0
//Sheet sheet = workbook.createSheet();
// 创建工作表
    Sheet sheet = workbook.createSheet("题库");
    Row row = sheet.createRow(0);
    row.createCell(0).setCellValue("题目标题");
    row.createCell(1).setCellValue("题目解答");
    row.createCell(2).setCellValue("题目难度等级");
    row.createCell(3).setCellValue("排序");
    row.createCell(4).setCellValue("副标题");
    row.createCell(5).setCellValue("题目类型");
    row.createCell(6).setCellValue("是否显示");
    List<QuestionEntity> list = this.list();
    for (int i = 0; i < list.size(); i++) {
        Row row2 = sheet.createRow(i + 1);
//创建表格
        row2.createCell(0).setCellValue(list.get(i).getTitle());
        row2.createCell(1).setCellValue(list.get(i).getAnswer());
        row2.createCell(2).setCellValue(list.get(i).getLevel());
        row2.createCell(3).setCellValue(list.get(i).getDisplayOrder());
        row2.createCell(4).setCellValue(list.get(i).getSubTitle());
        row2.createCell(5).setCellValue(list.get(i).getType());
        row2.createCell(6).setCellValue(list.get(i).getEnable());
    }
    return workbook;
}
    @Autowired
    private QuestionDao questionDao;
    @Override
    public List<Map<String, Object>> countType() {
        QueryWrapper<QuestionEntity> queryWrapper=new QueryWrapper<>();
        queryWrapper.select("TYPE,count(*) as num").groupBy("TYPE");
        List<Map<String, Object>> list = questionDao.selectMaps(queryWrapper);
        return list;
    }

}